﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace YJ.Data.MSSQL.Areas
{
    public class CRConferenceSign : YJ.Data.Interface.Areas.ICRConferenceSign
    {
        private DBHelper dbHelper = new DBHelper();
        /// <summary>
        /// 构造函数
        /// </summary>
        public CRConferenceSign()
        {
        }
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="model">YJ.Data.Model.Areas.CRConferenceSign实体类</param>
        /// <returns>新增记录的ID</returns>
        public int Add(YJ.Data.Model.Areas.CRConferenceSign model)
        {
            string sql = @"INSERT INTO CRConferenceSign
				(CRMeetingID,UserID,UserName,SignDate,CreationTime,Note) 
				VALUES(@CRMeetingID,@UserID,@UserName,@SignDate,@CreationTime,@Note);
				SELECT SCOPE_IDENTITY();";
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@CRMeetingID", SqlDbType.VarChar, 50){ Value = model.CRMeetingID },
                new SqlParameter("@UserID", SqlDbType.VarChar, 50){ Value = model.UserID },
                model.UserName == null ? new SqlParameter("@UserName", SqlDbType.NVarChar, 100) { Value = DBNull.Value } : new SqlParameter("@UserName", SqlDbType.NVarChar, 100) { Value = model.UserName },
                model.SignDate == null ? new SqlParameter("@SignDate", SqlDbType.DateTime, 8) { Value = DBNull.Value } : new SqlParameter("@SignDate", SqlDbType.DateTime, 8) { Value = model.SignDate },
                model.CreationTime == null ? new SqlParameter("@CreationTime", SqlDbType.DateTime, 8) { Value = DBNull.Value } : new SqlParameter("@CreationTime", SqlDbType.DateTime, 8) { Value = model.CreationTime },
                model.Note == null ? new SqlParameter("@Note", SqlDbType.NVarChar, 100) { Value = DBNull.Value } : new SqlParameter("@Note", SqlDbType.NVarChar, 100) { Value = model.Note }
            };
            int maxID;
            return int.TryParse(dbHelper.ExecuteScalar(sql, parameters), out maxID) ? maxID : -1;
        }
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="model">YJ.Data.Model.Areas.CRConferenceSign实体类</param>
        public int Update(YJ.Data.Model.Areas.CRConferenceSign model)
        {
            string sql = @"UPDATE CRConferenceSign SET 
				CRMeetingID=@CRMeetingID,UserID=@UserID,UserName=@UserName,SignDate=@SignDate,CreationTime=@CreationTime,Note=@Note
				WHERE ID=@ID";
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@CRMeetingID", SqlDbType.VarChar, 50){ Value = model.CRMeetingID },
                new SqlParameter("@UserID", SqlDbType.VarChar, 50){ Value = model.UserID },
                model.UserName == null ? new SqlParameter("@UserName", SqlDbType.NVarChar, 100) { Value = DBNull.Value } : new SqlParameter("@UserName", SqlDbType.NVarChar, 100) { Value = model.UserName },
                model.SignDate == null ? new SqlParameter("@SignDate", SqlDbType.DateTime, 8) { Value = DBNull.Value } : new SqlParameter("@SignDate", SqlDbType.DateTime, 8) { Value = model.SignDate },
                model.CreationTime == null ? new SqlParameter("@CreationTime", SqlDbType.DateTime, 8) { Value = DBNull.Value } : new SqlParameter("@CreationTime", SqlDbType.DateTime, 8) { Value = model.CreationTime },
                model.Note == null ? new SqlParameter("@Note", SqlDbType.NVarChar, 100) { Value = DBNull.Value } : new SqlParameter("@Note", SqlDbType.NVarChar, 100) { Value = model.Note },
                new SqlParameter("@ID", SqlDbType.Int, -1){ Value = model.ID }
            };
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        public int Delete(int id)
        {
            string sql = "DELETE FROM CRConferenceSign WHERE ID=@ID";
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@ID", SqlDbType.Int){ Value = id }
            };
            return dbHelper.Execute(sql, parameters);
        }
        /// <summary>
        /// 将DataRedar转换为List
        /// </summary>
        private List<YJ.Data.Model.Areas.CRConferenceSign> DataReaderToList(SqlDataReader dataReader)
        {
            List<YJ.Data.Model.Areas.CRConferenceSign> List = new List<YJ.Data.Model.Areas.CRConferenceSign>();
            YJ.Data.Model.Areas.CRConferenceSign model = null;
            while (dataReader.Read())
            {
                model = new YJ.Data.Model.Areas.CRConferenceSign();
                model.ID = dataReader.GetInt32(0);
                model.CRMeetingID = dataReader.GetString(1);
                model.UserID = dataReader.GetString(2);
                if (!dataReader.IsDBNull(3))
                    model.UserName = dataReader.GetString(3);
                if (!dataReader.IsDBNull(4))
                    model.SignDate = dataReader.GetDateTime(4);
                if (!dataReader.IsDBNull(5))
                    model.CreationTime = dataReader.GetDateTime(5);
                if (!dataReader.IsDBNull(6))
                    model.Note = dataReader.GetString(6);
                List.Add(model);
            }
            return List;
        }
        /// <summary>
        /// 查询所有记录
        /// </summary>
        public List<YJ.Data.Model.Areas.CRConferenceSign> GetAll()
        {
            string sql = "SELECT * FROM CRConferenceSign";
            SqlDataReader dataReader = dbHelper.GetDataReader(sql);
            List<YJ.Data.Model.Areas.CRConferenceSign> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List;
        }
        /// <summary>
        /// 查询记录数
        /// </summary>
        public long GetCount()
        {
            string sql = "SELECT COUNT(*) FROM CRConferenceSign";
            long count;
            return long.TryParse(dbHelper.GetFieldValue(sql), out count) ? count : 0;
        }
        /// <summary>
        /// 根据主键查询一条记录
        /// </summary>
        public YJ.Data.Model.Areas.CRConferenceSign Get(int id)
        {
            string sql = "SELECT * FROM CRConferenceSign WHERE ID=@ID";
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@ID", SqlDbType.Int){ Value = id }
            };
            SqlDataReader dataReader = dbHelper.GetDataReader(sql, parameters);
            List<YJ.Data.Model.Areas.CRConferenceSign> List = DataReaderToList(dataReader);
            dataReader.Close();
            return List.Count > 0 ? List[0] : null;
        }
    }
}